package db;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import java.util.Properties;

public class DBManager {
	private String _driver = "org.hsqldb.jdbcDriver";
	private String _protocol = "jdbc:hsqldb:";
	private String _url = "hsql://localhost/";
	private static DBManager _dbManager = null;
	private Statement _statement;
	private Connection _conn;


	private DBManager(){
		openConnection();
	}

	public Connection getConnection(){
		return _conn;
	}

	public static DBManager getDBManager(){
		if(_dbManager == null)
			_dbManager = new DBManager();

		return _dbManager;
	}

	public ResultSet query(String query){
		ResultSet res = null;

		try {
			_statement = this.getConnection().createStatement();
			res = _statement.executeQuery(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return res;
	}

	public boolean executeQuery(String query){

		boolean res = false;
		try {
			_statement = this.getConnection().createStatement();
			res = _statement.execute(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public Statement getStatement(){
		return _statement;
	}

	public void closeConnection() {
		try {
			if(_statement != null)
				_statement.close();
			_conn.commit();
			_conn.close();

		} catch (Throwable e) {
			System.out.println("exception thrown:");

			if (e instanceof SQLException)
				printSQLError((SQLException) e);
			else
				e.printStackTrace();
		}
	}

	public void createDBSchema(){

		this.openConnection();

		String query =  "CREATE TABLE Posts"+
						"(" +
						"ID int  GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),"+
						"USERNAME varchar(30),"+
						"PUBDATE timestamp,"+
						"TITLE varchar(100)," +
						"CONTENT varchar(500),"+
						"TAGS varchar(100),"+
						"primary key (ID),"+
						")";

		this.executeQuery(query);
		this.closeConnection();
	}

	public void dropUsers(){
		this.openConnection();
		this.executeQuery("drop table USERS");
		this.closeConnection();
	}

	public void dropPosts(){
		this.openConnection();
		this.executeQuery("drop table POSTS");
		this.closeConnection();
	}


	public int getNumberOfPosts(){
		this.openConnection();
		ResultSet res = this.query("SELECT COUNT(*) FROM Posts");

		int count = 0;
		try {
			count = res.getInt(1);
			res.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.closeConnection();
		return count;
	}

	public void openConnection(){
		System.out.println("Connecting to database server " + _url);

		try {
			// The driver is installed by loading its class
			Class.forName(_driver).newInstance();
			System.out.println("Loaded the jdbc driver " + _driver);

			String user = "sa";
			String password = "";

			Properties props = new Properties();
			props.put("user", user);
			props.put("password", password);
			_conn = DriverManager.getConnection(_protocol + _url, props);
			System.out.println("Connected to server " + _protocol + _url);

		} catch (Throwable e) {
			System.out.println("exception thrown:");

			if (e instanceof SQLException)
				printSQLError((SQLException) e);
			else
				e.printStackTrace();
		}
	}

	static void printSQLError(SQLException e) {
		while (e != null) {
			System.out.println(e.toString());
			e = e.getNextException();
		}
	}

//		public static void main(String[] args){
//			DBManager d = DBManager.getDBManager();
//			//d.dropPosts();
//			//d.dropUsers();
//			d.createDBSchema();
//		}
}